#!/bin/bash

APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 

dim_user_zip="
insert overwrite table ${APP}.dim_user_zip partition(dt='9999-12-31')
select
	user_1.id,
	login_name,
	nick_name,
	md5(passwd),
	md5(real_name),
	md5(phone_num),
	md5(email),
	head_img,
	user_level,
	birthday,
	gender,
	create_time,
	update_time,
	user_1.status,
	vip_change.id,
	from_vip,
	to_vip,
	date_format(create_time,'yyyy-MM-dd') start_date,
	'9999-12-31' end_date
from (
		select
			data.id,
			data.login_name,
			data.nick_name,
			data.passwd,
			data.real_name,
			data.phone_num,
			data.email,
			data.head_img,
			data.user_level,
			data.birthday,
			data.gender,
			data.create_time,
			data.update_time,
			data.status
		from ${APP}.ods_user_info_inc
		where dt = '$do_date'
		and type = 'bootstrap-insert'
) user_1
left join (
    select
			data.id,
			data.user_id,
			data.from_vip,
			data.to_vip
		from ${APP}.ods_vip_change_detail_inc
		where dt = '$do_date'
		and type = 'bootstrap-insert'
) vip_change
on user_1.id = vip_change.user_id;
"

dim_course_full="
with
course as
(
		select
			id,
			course_name,
			subject_id,
			teacher,
			publisher_id,
			chapter_num,
			origin_price,
			reduce_amount,
			actual_price,
			create_time,
			update_time,
			deleted
		from ${APP}.ods_course_info_full
		where dt='$do_date'
),
chapter as
(
		select
			id,
			chapter_name,
			course_id,
			video_id,
			is_free
		from ${APP}.ods_chapter_info_full
		where dt = '$do_date'
),
video as
(
		select
			id,
			video_name,
			during_sec
		from ${APP}.ods_video_info_full
		where dt='$do_date'

),
subject as
(
		select
			id,
			subject_name,
			category_id
		from ${APP}.ods_base_subject_info_full
		where dt = '$do_date'

),
category as
(
		select
			id,
			name
		from ${APP}.ods_base_category_info_full
		where dt = '$do_date'
)
insert overwrite table ${APP}.dim_course_full partition(dt='$do_date')
select
	course.id,
	course.course_name,
	course.subject_id,
	course.teacher,
	course.publisher_id,
	course.chapter_num,
	course.origin_price,
	course.reduce_amount,
	course.actual_price,
	chapter.id,
	chapter.chapter_name,
	chapter.is_free,
	subject.subject_name,
	subject.category_id,
	category.name,
	chapter.video_id,
	video.video_name,
	video.during_sec,
	course.create_time,
	course.update_time,
	course.deleted
from course
left join chapter on course.id=chapter.course_id
left join subject on course.subject_id = subject.id
left join category on subject.category_id = category.id
left join video on chapter.video_id = video.id;
"

dim_province_full="
insert overwrite table ${APP}.dim_province_full
select
	id,
	name,
	region_id,
	area_code,
	iso_code,
	iso_3166_2,
    dt
from ${APP}.ods_base_province_full
where dt='$do_date';
"
dim_question_full="
with
question_info as
(
		select
			id,
			question_txt,
			chapter_id,
			course_id,
			question_type,
			creat_time,
			updata_time,
			publisher,
			deleted
		from ${APP}.ods_test_question_info_full
		where dt = '$do_date'
),
question_option as
(
		select
			id,
			option_txt,
			question_id,
			is_correct
		from ${APP}.ods_test_question_option_full
		where dt = '$do_date'
),
paper_question as
(
		select
			id,
			paper_id,
			score
		from ${APP}.ods_test_paper_question_full
		where dt = '$do_date'
),
paper as
(
		select
			id,
			paper_title
		from ${APP}.ods_test_paper_full
		where dt = '$do_date'
),
point_question as
(
		select
			id,
			point_id,
			question_id
		from ${APP}.ods_test_point_question_full
		where dt = '$do_date'
),
knowledge_point as
(
		select
			id,
			point_txt,
			point_level
		from ${APP}.ods_knowledge_point_full
		where dt = '$do_date'
)
insert overwrite table ${APP}.dim_question_full partition(dt='$do_date')
select
	question_info.id,
    question_info.question_txt,
    question_info.chapter_id,
    question_info.course_id,
    question_info.question_type,
	question_option.option_txt,
	question_option.is_correct,
	paper_question.paper_id,
	paper_question.score,
	paper.paper_title,
	point_question.point_id,
	knowledge_point.point_txt,
 knowledge_point.point_level,
   question_info.creat_time,
    question_info.updata_time,
    question_info.publisher,
    question_info.deleted
from question_info
left join question_option on question_info.id=question_option.question_id
left join paper_question on question_info.id = question_option.question_id
left join paper on paper_question.paper_id = paper.id
left join point_question on question_info.id = point_question.id
left join knowledge_point on point_question.point_id = knowledge_point.id;
"

case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_question_full")
    hive -e "$dim_question_full"
;;
"all")
    hive -e "$dim_user_zip$dim_course_full$dim_province_full$dim_question_full"
;;
esac
